package com.database;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBCallHistory {
	public static final String KEY_ROWID = "Call_ID";
	public static final String PHONENUMBER = "BM_Phone_Number";
	public static final String CALLDATE = "BM_Call_Date";

	public static final String KEY_DETAILID = "Detail_ID";
	public static final String CALLHISID = "BM_Call_History_ID";
	public static final String HISTORYCONTENT = "BM_History_Content";

	public static final String DATABASE_NAME = "BM_CALL";
	public static final String DATABASE_TABLE_CALLHISTORY = "BM_CALL_HISTORY";
	public static final String DATABASE_TABLE_CALLHISTORYDETAIL = "BM_CALL_HISTORY_DETAIL";
	public static final int DATABASE_VERSION = 1;

	private DbHelper kHelper;
	private final Context kContext;
	private SQLiteDatabase kDatabase;

	private static class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			String query = "create table " + DATABASE_TABLE_CALLHISTORY + " ("
					+ KEY_ROWID + " integer primary key AUTOINCREMENT,"
					+ PHONENUMBER + " text not null, " + CALLDATE
					+ " datetime not null " + "); ";
			db.execSQL(query);

			String query1 = "create table " + DATABASE_TABLE_CALLHISTORYDETAIL
					+ " (" + KEY_DETAILID
					+ " integer primary key AUTOINCREMENT," + CALLHISID
					+ "  integer not null, " + HISTORYCONTENT + "  null, "
					+ "FOREIGN KEY(" + CALLHISID + ") REFERENCES "
					+ DATABASE_TABLE_CALLHISTORY + "(" + KEY_ROWID + ") "
					+ "); ";
			db.execSQL(query1);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("drop table if exists " + DATABASE_TABLE_CALLHISTORY);
			onCreate(db);

			db.execSQL("drop table if exists "
					+ DATABASE_TABLE_CALLHISTORYDETAIL);
			onCreate(db);
		}
	}

	public DBCallHistory(Context c) {
		kContext = c;
	}

	public DBCallHistory open() {
		kHelper = new DbHelper(kContext);
		kDatabase = kHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		kHelper.close();
	}

	@SuppressLint("SimpleDateFormat")
	public int InsertCallHistory(String strPhoneNumber) {
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		Date date = new Date();
		ContentValues cv = new ContentValues();
		cv.put(PHONENUMBER, strPhoneNumber);
		cv.put(CALLDATE, dateFormat.format(date));
		kDatabase.insert(DATABASE_TABLE_CALLHISTORY, null, cv);

		Cursor cur = kDatabase.rawQuery("select MAX(" + KEY_ROWID + ") FROM "
				+ DATABASE_TABLE_CALLHISTORY + "", null);
		cur.moveToFirst();
		int ID = cur.getInt(0);
		cur.close();
		return ID;
	}

	public Cursor SelectCallHistoryAll() {
		return kDatabase.query(DATABASE_TABLE_CALLHISTORY, new String[] {
				KEY_ROWID, PHONENUMBER, CALLDATE }, null, null, null, null,
				CALLDATE + " DESC");
	}

	public void InsertCallHistoryDetail(String strCallHisID,
			String strHisContent) {
		ContentValues cv = new ContentValues();
		cv.put(CALLHISID, strCallHisID);
		cv.put(HISTORYCONTENT, strHisContent);
		kDatabase.insert(DATABASE_TABLE_CALLHISTORYDETAIL, null, cv);
	}

	public Cursor SelectCallHisDetailAll(String strCallID) {
		return kDatabase.query(DATABASE_TABLE_CALLHISTORYDETAIL, new String[] {
				KEY_DETAILID, CALLHISID, HISTORYCONTENT }, CALLHISID + "="
				+ strCallID, null, null, null, null);
	}

	public Cursor SelectCallHisDetailTop() {
		String sql = " SELECT " + KEY_DETAILID + "," + PHONENUMBER + ","
				+ HISTORYCONTENT + "," + CALLDATE + " FROM ( SELECT "
				+ KEY_DETAILID + "," + PHONENUMBER + "," + HISTORYCONTENT + ","
				+ CALLDATE + " FROM " + DATABASE_TABLE_CALLHISTORY + " a ,"
				+ DATABASE_TABLE_CALLHISTORYDETAIL + " b WHERE a." + KEY_ROWID
				+ " = b." + CALLHISID + " ORDER BY " + CALLDATE
				+ " ASC LIMIT 50) ORDER BY " + CALLDATE + " ASC ";

		return kDatabase.rawQuery(sql, null);
	}

	public void DeleteCallHistory() {
		String sql = " DELETE FROM " + DATABASE_TABLE_CALLHISTORY;
		kDatabase.execSQL(sql);
	}

	public void DeleteCallHistoryDetail() {
		String sql = " DELETE FROM " + DATABASE_TABLE_CALLHISTORYDETAIL;
		kDatabase.execSQL(sql);
	}

	public Cursor SelectCallHisDetail(String keyDetail) {
		String sql = "  SELECT " + CALLHISID + " FROM "
				+ DATABASE_TABLE_CALLHISTORYDETAIL + " WHERE " + KEY_DETAILID
				+ " = '" + keyDetail + "'  LIMIT 1";
		return kDatabase.rawQuery(sql, null);
	}

	public void DeleteCallHistoryByID(String idCallHis) {
		String sql = " DELETE FROM " + DATABASE_TABLE_CALLHISTORY + " WHERE "
				+ KEY_ROWID + " = '" + idCallHis + "'";
		kDatabase.execSQL(sql);
	}

	public void DeleteCallHistoryDetailByID(String idCallHisDetail) {
		String sql = " DELETE FROM " + DATABASE_TABLE_CALLHISTORYDETAIL
				+ " WHERE " + KEY_DETAILID + " = '" + idCallHisDetail + "'";
		;
		kDatabase.execSQL(sql);
	}
}
